package db_layer;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import model_layer.Supplier;

/**
 * Provides access to management of the data of Suppliers, stored in the system.
 * @author Miroslav Marinov
 *
 */
public class SupplierDB
{
	private Connection instance;
	
	/**
	 * SupplierDB's constructor gets the Connection instance of the DBConnection class.
	 * DBConnection is designed so it provides only one connection(Singleton).
	 */
	public SupplierDB()
	{
		instance = DBConnection.get_instance().get_connection();
	}
	
	/**
	 * Method for finding a supplier by given Integer field (e.g. id)
	 * @param var_name -> String with the name of the variable used to find a Supplier 
	 * @param val -> String with the value of the variable used to find a Supplier
	 */
	public Supplier find_supplier(String var_name, int val)
	{
		return single_find(var_name, "", val);
	}
	
	/**
	 * Method for finding a supplier by given String field(e.g. name).
	 * @param var_name -> String with the name of the variable used to find a Supplier 
	 * @param val -> String with the value of the variable used to find a Supplier
	 */
	public Supplier find_supplier(String var_name, String val)
	{
		return single_find(var_name, val, -1);
	}
	
	/**
	 * Method for inserting a new supplier into the database.
	 * @param suppl -> variable of type Supplier, containing all the needed data, that will be added to the database
	 * @return a boolean statement that indicates if the supplier was inserted successfully (true - if successful, false - not successful).
	 */
	public boolean insert_supplier(Supplier suppl)
	{
		boolean success = false;	// variable for storing the statement whether the insert was successful or not.
		
		// All Supplier's data is divided in separate variables for better code readability and easier further maintenance of the code.
		String phone_nr = suppl.getPhone_nr();	
		String address = suppl.getAddress();
		String city = suppl.getCity();
		String email = suppl.getEmail();
		String name = suppl.getName();
		String zipcode = suppl.getZipcode();
		String description = suppl.getDescription();
		String cvr = suppl.getCvr();
		Boolean deleted = suppl.getDeleted();
		
		try
		{
			/*
			 * Inserting a supplier is implemented with transactions, because the operation requires series of insert statements,
			 * and in case of inserting only parts of the data of the supplier will lead to Database inconsistency.
			 */
			DBConnection.begin_transaction();// starting a transaction -> nothing will be committed until everything is added.
			manage_zipcode(zipcode, city);	// inserting supplier's zipcode in Zipcode table(updating in case it already exists).
			// Creating an insert statement using the supplementary DBUtilities class.
			PreparedStatement st = DBUtilities.make_insert_statement(instance, "Suppliers", "phone_nr, sup_address, email, name, zipcode, descript, CVR, deleted");	
				
			st.setString(1, phone_nr);// Here we set all variables of the PreparedStatement. They need to be set before we can execute the statement.
			st.setString(2, address);		// This cool feature however comes at a price of setting every variable in the statement beforehand
			st.setString(3, email);
			st.setString(4, name);
			st.setString(5, zipcode);
			st.setString(6, description);
			st.setString(7, cvr);
			st.setBoolean(8, deleted);
			st.setQueryTimeout(5);	// Setting a timeout for the query - not always needed, but preferably used in this case.
			st.executeUpdate();				// Executing an update (in this case - an insert)
			st.close();						// Closing statement to make sure none of the resources leak.
			DBConnection.end_transaction();	// Ending the transaction after all the previous statements were executed.
			success = true;					// If everything went all right(none of the statements threw exception), the transaction is marked as successful
			
		}
		catch(SQLException e)	// Catching an SQL exception if something went wrong.
		{
			System.err.println("Error while inserting a supplier: " + e);	//Printing the error
			DBConnection.rollback_transaction();	// In case any changes were made we roll back(cancel) all of them and protect the database from harmful actions
													// If such failure occurs, this is an indicator that important further investigation
													// and possible fix of the problem must be provided.
		}
		return success;	// returns boolean statement whether it was successful or not.
	}
	/**
	 * Method for update an existing supplier in the database.
	 * The return value is provided by the executeUpdate method in the PreparedStatement class -
	 * if the operation was successful it returns number of rows of SQL manipulation statements.
	 * Because of similarities in actions, read insert_supplier() method's documentation for clear understanding. 
	 * @param suppl -> variable of type Supplier, containing all the needed new data, that will be updated in the database.
	 * @return a Boolean value that indicates if the supplier was updated successfully.
	 */
	public boolean update_supplier(Supplier suppl)
	{
		int update = -1;
		String id = suppl.getId();
		String phone_nr = suppl.getPhone_nr();
		String address = suppl.getAddress();
		String city = suppl.getCity();
		String email = suppl.getEmail();
		String name = suppl.getName();
		String zipcode = suppl.getZipcode();
		String description = suppl.getDescription();
		String cvr = suppl.getCvr();
		Boolean deleted = suppl.getDeleted();
		
		try
		{
			DBConnection.begin_transaction();
			manage_zipcode(zipcode, city);
			PreparedStatement ps = DBUtilities.make_update_statement(instance, "Suppliers", "phone_nr, sup_address, email, name, zipcode, descript, CVR, deleted","id");
			ps.setString(1, phone_nr);
			ps.setString(2, address);
			ps.setString(3, email);
			ps.setString(4, name);
			ps.setString(5, zipcode);
			ps.setString(6, description);
			ps.setString(7, cvr);
			ps.setBoolean(8, deleted);
			ps.setString(9, id);
			ps.setQueryTimeout(5);
			update = ps.executeUpdate();
			ps.close();
			DBConnection.end_transaction();
		}
		catch(Exception e)
		{
			System.err.println("Error while updating a supplier: " + e);
			DBConnection.rollback_transaction();
			
		}
		return update == 1;
	}
	
	/**
	 * Method used for inserting/updating a Zip code in the ZipCity table in the database.
	 *
	 * @param zipcode String value of the Zip Code
	 * @param city String value of the city with this Zip Code
	 * @throws SQLException throws SQL exception in case any of the steps failed and in case the Zip code couldn't be added.
	 * This helps other methods to prevent inconsistency in the database.
	 */
	private void manage_zipcode(String zipcode, String city) throws SQLException
	{
		//Creates a prepared String Query that checks if the Zip Code exists.
		//If it does - it updates the City, and if it does not -
		// it adds the Zip Code and the city to the database.
		String query = "IF exists (SELECT * FROM ZipCity WHERE zipcode = ?) "
						+	"BEGIN "
						+	   "UPDATE ZipCity SET city = ? "
						+	   "WHERE zipcode = ? "
						+	"END "
						+	"ELSE "
						+	"BEGIN "
						+	   "INSERT INTO ZipCity(zipcode, city) VALUES (?, ?) "
						+	"END";

		PreparedStatement ps = instance.prepareStatement(query);
		ps.setString(1, zipcode);
		ps.setString(2, city);
		ps.setString(3, zipcode);
		ps.setString(4, zipcode);
		ps.setString(5, city);
		ps.setQueryTimeout(5);
		ps.executeUpdate();
		ps.close();
	}
	
	
	/**
	 * Method used for finding a supplier, under desired conditions.
	 * s_val contains String(e.g. name of Supplier) that supplier will be found by and if it is an empty String,
	 * then supplier will be found by i_var, which contains Integer value (e.g. id of the Supplier).
	 * @param var_name -> name of the variable by which the method will be searching for a Supplier
	 * @param s_val String value of the variable
	 * @param i_val -> Integer value of the variable
	 * @return suppl -> found supplier. If more than one supplier matches, it returns the first one found.
	 * 					If there was an error while running a function or if no matches were found the function returns null.
	 */
	private Supplier single_find(String var_name, String s_val, int i_val)
	{
		String query = make_query(); 	
		ResultSet results;
		Supplier suppl = null;
		try
		{
			/* A PreparedStatement is created with UtilityFunctions method prepare_statement
			 PreparedStatement is much better than a normal Statement because it is resistant to SQL Injections
			 making it much more secure mean of accessing the database than a normal Statement.
			 A statement is a java representation of a query - it works the same as an SQL query. */
			PreparedStatement ps = DBUtilities.prepare_statement(instance, query, var_name, s_val, i_val);
			ps.setQueryTimeout(5);
			results = ps.executeQuery();	//Executing the prepared query and setting the results to a ResultSet variable
			if(results.next())		//if one or more matches are found
			{
				suppl = create_supplier(results);
			}
			ps.close();			// Closing the statement after all the operations are done.
		}
		catch(SQLException e)
		{
			System.err.println("Supplier query exception: " + e);	//An exception is printed in case of such
		}
		return suppl;	
	}
	
	/**
	 * Method for getting all suppliers with certain specifications. In case an error occurs or there are no matches,
	 * an empty ArrayList will be returned.
	 * @param var_name -> name of the variable by which the method will be searching for a Supplier
	 * @param s_val String value of the variable
	 * @param i_val -> Integer value of the variable
	 * @return suppliers -> An arrayList with all suppliers matching the given criteria.
	 * 						If there was an error while running a function or if no matches were found the function returns null.
	 */
	public ArrayList<Supplier> multiple_find(String var_name, String s_val, int i_val)
	{
		String query = make_query();
		ResultSet results;
		ArrayList<Supplier> suppliers = new ArrayList<Supplier>();
		Supplier suppl = null;
		try
		{
			PreparedStatement ps = DBUtilities.prepare_statement(instance, query, var_name, s_val, i_val);
			ps.setQueryTimeout(5);
			results = ps.executeQuery();
			while(results.next())		// Creating ResultSets for all suppliers and adding them to the ArrayList
			{
				suppl = create_supplier(results);
				suppliers.add(suppl);				
			}
			ps.close();
		}
		catch(SQLException e)
		{
			//in case an exception has been thrown, the ArrayList is cleared and returned as an empty one.
			suppliers.clear();
			System.err.println("Supplier Query Exception: " + e);
		}
		return suppliers;
	}
	
	/**
	 * Method for creating a query for searching the database for a desired supplier.
	 */
	private String make_query()
	{
		return DBUtilities.make_query("su.id, su.name, su.phone_nr, su.email, su.sup_address, su.zipcode, z.city, su.descript, su.CVR, su.deleted",
				"ZipCity AS z, Suppliers AS su", "su.zipcode = z.zipcode");
	}
	
	/**
	 * Method for transforming Customer data from ResultSet to Object of type customer. 
	 * @return null if an error has occurred.
	 */
	private Supplier create_supplier(ResultSet results)
	{
		Supplier suppl = new Supplier();

		try
		{
			/*Here we set fields of a supplier with data extracted from the ResultSet.
			 We get them by the names of the fields from the select statements which are names of the columns unless we use the AS statement
			 (e.g if query was SELECT id ... we use: results.getInt("id"), but if it was SELECT id AS cust_id... we would use: results.getInt("cust_id"),
			 note that SELECT e.id ...  will still require to use results.getInt("id"), not results.getInt("e.id"))*/
			suppl.setId(results.getString("id"));
			suppl.setName(results.getString("name"));
			suppl.setAddress(results.getString("sup_address"));
			suppl.setZipcode(results.getString("zipcode"));
			suppl.setCity(results.getString("city"));
			suppl.setPhone_nr(results.getString("phone_nr"));
			suppl.setEmail(results.getString("email"));
			suppl.setDescription(results.getString("descript"));
			suppl.setCvr(results.getString("CVR"));
			suppl.setDeleted(results.getBoolean("deleted"));
		}
		catch(SQLException ex)
		{
			System.out.println("Error while creating a supplier: " + ex);
			return null;
		}
		
		return suppl;
	}
	
	public String find_city(String zipcode)
	{
		String query = DBUtilities.make_query("city", "ZipCity");
		ResultSet results;
		try
		{
			PreparedStatement ps = DBUtilities.prepare_statement(instance, query, "zipcode", zipcode);
			ps.setQueryTimeout(5);
			results = ps.executeQuery();
			if(results.next())
			{
				return results.getString("city");
			}
			ps.close();
		}
		catch(SQLException e)
		{
			System.err.println("Error occured while searching for city: " + e);
		}
		return null;
	}

}